package com.ideal.lx_mss.utils;



import com.ideal.lx_mss.common.DataInfo;
import com.ideal.lx_mss.mapper.MysqlUtilsMapper;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Component
public class TableFromJsonUtil {


    @Value("${spring.datasource.url}")
    private String databaseUrl;
    @Value("${spring.datasource.username}")
    private String userName;
    @Value("${spring.datasource.password}")
    private String passWord;

    @Autowired
    private MysqlUtilsMapper mysqlUtilsMapper;

    /**
     * 构造选择器
     */

    public void structure(String dbType,String jsonStr){
        if ("postgresql".equals(dbType)){
            tableFromJsonByPgDB(jsonStr);
        }

    }

    public void dataService(DataInfo dataInfo, String jsonStr){

        //判断表是否存在
        if (!tableExists(dataInfo.getItfcode())){ //不存在  建表
            structure(dataInfo.getDb_type(),jsonStr);
            insertTable(dataInfo.getItfcode(),dataInfo.getDataload());
        }else { //存在 存值
            insertTable(dataInfo.getItfcode(),dataInfo.getDataload());
        }

    }

    public boolean tableExists(String table) {
        String url = databaseUrl;
        String username = userName;
        String password = passWord;
        String tableName = table;

        try (Connection connection = DriverManager.getConnection(url, username, password);
             Statement statement = connection.createStatement()) {

            ResultSet resultSet = statement.executeQuery("SHOW TABLES LIKE '" + tableName + "'");

            if (resultSet.next()) {
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * pg数据库建表
     * @param jsonInput
     */
    public void tableFromJsonByPgDB(String jsonInput){
        // 假设 jsonInput 是从某处获取的 JSON 字符串
        JSONObject jsonObject = new JSONObject(jsonInput);
        String schemaName = jsonObject.getString("itfcode");
        JSONArray dataload = jsonObject.getJSONArray("dataload");
        //建表
        StringBuilder createTableSql = new StringBuilder("CREATE TABLE IF NOT EXISTS " + schemaName + " (\n");

        for (int i = 0; i < dataload.length(); i++) {
            JSONObject field = dataload.getJSONObject(i);
            String columnName = field.getString("columnname");
            String datatype = field.getString("datatype");

            switch (datatype) {
                case "VARCHAR":
                    createTableSql.append(columnName).append(" VARCHAR(").append(4000).append(") ").append(",\n");
                    break;
                case "BPCHAR":
                    createTableSql.append(columnName).append(" CHAR(").append(50).append(") ").append(",\n");
                    break;
                case "NUMERIC":
                    createTableSql.append(columnName).append(" DECIMAL(50, 0) ").append(",\n");
                    break;
                case "TIMESTAMP":
                    createTableSql.append(columnName).append(" TIMESTAMP ").append("NULL DEFAULT NULL").append(",\n");
                    break;
                // Add other cases for different datatypes as needed.
                default:
                    break;
            }
        }

        // Remove trailing comma and newline
        int lastCommaIndex = createTableSql.lastIndexOf(",");
        createTableSql = new StringBuilder(createTableSql.substring(0, lastCommaIndex) + "\n");



        createTableSql.append(");");

        // 打印出 CREATE TABLE 语句
        System.out.println(createTableSql.toString());

        // 接下来是使用 JDBC 连接数据库执行这个语句的示例
        String jdbcUrl = databaseUrl;
        String username = userName;
        String password = passWord;

        try {
            Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
            Statement stmt = conn.createStatement();

            // 执行 SQL 创建表语句
            stmt.execute(createTableSql.toString());

            // 关闭连接
            stmt.close();
            conn.close();

            System.out.println("Table created successfully");

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 数据插入数据
     * @param tableName
     * @param jsonArray
     */
    public void insertTable(String tableName,JSONArray jsonArray){

        //查询表结构
        List<String> tableColumns = mysqlUtilsMapper.getTableColumns(tableName);

        ArrayList<String> list = new ArrayList<>();
        ArrayList<String> errs = new ArrayList<>();
        //拼表字段
        String result = String.join(", ", tableColumns);

        for (int i = 0; i < jsonArray.length(); i++) {

            StringBuilder valueStr = new StringBuilder();
            StringBuilder insertStr = new StringBuilder();
            for (String tableColumn : tableColumns) {
                valueStr.append("\"").append(jsonArray.getJSONObject(i).get(tableColumn)).append("\"").append(",");
            }
            int lastCommaIndex = valueStr.lastIndexOf(",");
            valueStr = new StringBuilder(valueStr.substring(0, lastCommaIndex) + "\n");
            StringBuilder append = insertStr.append("insert into ").append(tableName).append("(").append(result).append(")").append("values").append("(").append(valueStr).append(")");
            list.add(append.toString());

        }

        System.out.println("");

        try {
            Connection conn = DriverManager.getConnection(databaseUrl, userName, passWord);
            Statement stmt = conn.createStatement();

            // 执行 SQL 创建表语句
            for (String s : list) {
                try {
                    stmt.execute(s);
                }catch (Exception e){
                    errs.add(s);
                }

            }


            // 关闭连接
            stmt.close();
            conn.close();

            System.out.println("Data insert successfully");

        } catch (Exception e) {
            e.printStackTrace();
        }



    }









}
